#include "UserDao.h"
#include "qlsdbconnpool.h"

#include <iostream>

/* static */
QCache<QString, User> UserDao::userCache;
QCache<QString, QList<User>> UserDao::usersCache;

UserDao::UserDao()
{

}

/* 根据id查找 */
User *UserDao::findOne(const quint32 &id)
{
	/* 缓存 */
	QString key = QString("findOne%1").arg(id);
	if (userCache.contains(key))
		return userCache.object(key);

	/* 组合sql语句 */
	QString content = QString("select * from `user` where id = %1").arg(id);

	//获得查询结果
	QSqlRecord* rec = QLsDBConnPool::runOne(content);

	//判空
	if(rec == nullptr) return nullptr;

	//将查询的结果存入User类的对象中
	User* user = new User();
	user->setId(rec->value("id").toUInt());
	user->setUsername(rec->value("username").toString());
	user->setPassword(rec->value("password").toString());
	user->setMobile(rec->value("mobile").toString());
	user->setEmail(rec->value("email").toString());
	user->setDate(rec->value("date").toString());

	/* 加入缓存 */
	userCache.insert(key, user);

	return user;
}

/* 根据自定义条件查找 */
User *UserDao::findOne(const QLsSpecification &spec)
{
	/* 缓存 */
	if (userCache.contains("findOne" + spec.getSqlWhere()))
		return userCache.object("findOne" + spec.getSqlWhere());

	//构建sql语句
	QString content = QString("select * from `user` where %1").arg(spec.getSqlWhere());

	//获得查询结果
	QSqlRecord* rec = QLsDBConnPool::runOne(content);

	//判空
	if(rec == nullptr) return nullptr;

	//将查询的结果存入User类的对象中
	User* user = new User;
	user->setId(rec->value("id").toUInt());
	user->setUsername(rec->value("username").toString());
	user->setPassword(rec->value("password").toString());
	user->setMobile(rec->value("mobile").toString());
	user->setEmail(rec->value("email").toString());
	user->setDate(rec->value("date").toString());

	/* 加入缓存 */
	userCache.insert("findOne" + spec.getSqlWhere(), user);

	return user;
}

QList<User> UserDao::findAll(const QLsSpecification &spec)
{
	if (usersCache.contains("findAll" + spec.getSqlWhere()))
		return *usersCache.object("findAll" + spec.getSqlWhere());

	QList<User> res;

	//构建sql语句
	QString content = QString("select * from `user` %1").arg(
				(spec.getSqlWhere() == "" ? "" : spec.getSqlWhere()));

	//获得查询结果
	QList<QSqlRecord> rec = QLsDBConnPool::runAll(content);

	//将查询的结果存入QList<User>链表中
	for(auto it = rec.begin(); it != rec.end(); it++)
	{
		User user;
		user.setId(it->value("id").toUInt());
		user.setUsername(it->value("username").toString());
		user.setPassword(it->value("password").toString());
		user.setMobile(it->value("mobile").toString());
		user.setEmail(it->value("email").toString());
		user.setDate(it->value("date").toString());
		res.push_back(user);
	}

	/* 加入缓存 */
	usersCache.insert("findAll" + spec.getSqlWhere(), &res);

	return res;
}

/* 保存 */
void UserDao::save(const User *user)
{
	//先判断该用户数据是否存在，以主键id来判断
	QString content = QString("select * from `user` where id = %1").arg(user->getId());
	QSqlRecord* rec = QLsDBConnPool::runOne(content);

	content.clear();
	if(rec == nullptr)
	{   //如果不存在，则insert
		content = QString("insert into `user` values(null, %1, %2, %3, %4, %5)").arg(
					QLsSpecification::tranString(user->getUsername())).arg(
					QLsSpecification::tranString(user->getPassword())).arg(
					QLsSpecification::tranString(user->getMobile())).arg(
					QLsSpecification::tranString(user->getEmail())).arg(
					QLsSpecification::tranString(user->getDate()));
		QLsDBConnPool::runNo(content);
	}
	else
	{   //如果存在，则update
		content = QString("update `user` set username = %1, password = %2, mobile = %3, email = %4,"
						  "date = %5 where id = %6").arg(
					QLsSpecification::tranString(user->getUsername())).arg(
					QLsSpecification::tranString(user->getPassword())).arg(
					QLsSpecification::tranString(user->getMobile())).arg(
					QLsSpecification::tranString(user->getEmail())).arg(
					QLsSpecification::tranString(user->getDate())).arg(user->getId());
		QLsDBConnPool::runNo(content);
	}

	/* 清理缓存 */
	userCache.clear();
	usersCache.clear();
}

/* 删除 */
void UserDao::del(const quint32 &id)
{
	//先判断该用户数据是否存在，以主键id来判断
	QString content = QString("select * from `user` where id = %1").arg(id);
	QSqlRecord* rec = QLsDBConnPool::runOne(content);

	content.clear();
	if(rec == nullptr)
	{   //判空
		qDebug() << "无此用户";
		return;
	}

	content = QString("delete from `user` where id = %1").arg(id);
	QLsDBConnPool::runNo(content);

	/* 清理缓存 */
	userCache.clear();
	usersCache.clear();
}

/* 查询表是否存在 */
bool UserDao::exists(const QString &databaseType)
{
	if(databaseType == "MySQL")
	{
		QSqlRecord *rec = QLsDBConnPool::runOne(
					"select * "
					"from information_schema.tables "
					"where table_schema='qlpmsp' and table_name = 'user';");
		return rec;
	}
	else if(databaseType == "SQLite")
	{
		QSqlRecord *rec = QLsDBConnPool::runOne(
					"select * "
					"from sqlite_master "
					"where type = 'table' and name = 'user';");
		return rec;
	}

	/* 清理缓存 */
	userCache.clear();
	usersCache.clear();

	return false;
}

/* 创建表 */
void UserDao::CreateTable(const QString &databaseType)
{
	/* mysql */
	if(databaseType == "MySQL")
	{
		/* 判断数据库是否存在，不存在则创建数据库，切换到该数据库 */
		QLsDBConnPool::runNo("create 'qlpmsp' if not exists `qlpmsp`;");
		QLsDBConnPool::runNo("use qlpmsp;");

		/* 如果表存在则删除之前的，创建新表 */
		QLsDBConnPool::runNo("DROP TABLE IF EXISTS `user`;");
		QLsDBConnPool::runNo(
					"CREATE TABLE `user` ("
					"  `id` int(11) NOT NULL AUTO_INCREMENT,"
					"  `username` varchar(16) DEFAULT NULL COMMENT '用户名',"
					"  `password` varchar(16) DEFAULT NULL COMMENT '密码',"
					"  `mobile` varchar(11) DEFAULT NULL COMMENT '手机号',"
					"  `email` varchar(48) DEFAULT NULL COMMENT '邮箱',"
					"  `date` varchar(19) DEFAULT NULL COMMENT '注册时间',"
					"  PRIMARY KEY (`id`)"
					") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
	}
	/* sqlite */
	else if(databaseType == "SQLite")
	{
		/* 如果表存在则删除之前的，创建新表 */
		QLsDBConnPool::runNo("DROP TABLE IF EXISTS `user`;");
		QLsDBConnPool::runNo(
					"CREATE TABLE `user` ("
					"  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,"
					"  `username` varchar(16) DEFAULT NULL,"
					"  `password` varchar(16) DEFAULT NULL,"
					"  `mobile` varchar(11) DEFAULT NULL,"
					"  `email` varchar(48) DEFAULT NULL,"
					"  `date` varchar(19) DEFAULT NULL"
					");");
	}

	/* 清理缓存 */
	userCache.clear();
	usersCache.clear();
}

